package com.Util;

import com.Bean.User;
import net.sf.json.JSONObject;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Base64;

public class UserDao extends DB {

    public static User getUser(String username, String passwd) {

        User user = null;
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("UserDao.getUser() processing");
            }

            PreparedStatement stmt = conn.prepareStatement("select * from `"+DBUSER+"` where username = ? and passwd = ?;");


            stmt.setString(1,username);
            stmt.setString(2,passwd);
            if (DB.isDebug()) {
                System.out.println(stmt);
            }

            ResultSet rs = stmt.executeQuery();

            if (rs.next())
                user = rs2class(rs);


        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public static User getUser(String username) {

        User user = null;
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("UserDao.getUser() processing");
            }

            PreparedStatement stmt = conn.prepareStatement("select * from `"+DBUSER+"` where username = ?;");

            stmt.setString(1,username);
            ResultSet rs = stmt.executeQuery();

            if (rs.next())
                user = rs2class(rs);


        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    private static User rs2class(ResultSet rs) {
        User user = new User();
        try {
            user.setUsername(rs.getString("username"));
            user.setPasswd(rs.getString("passwd"));
            user.setFamilyname(rs.getString("familyname"));
            user.setName(rs.getString("name"));
            user.setEmail(rs.getString("email"));
            user.setStaff(rs.getString("staff"));
            user.setMoney(rs.getString("money"));

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return user;
    }

    public static User selectUser(String username) {
        User user = null;
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("UserDao.selectuser() processing");
            }
            Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);

            ResultSet rs = stmt.executeQuery("select * from " + DBUSER + " where username='" + username + "'");

            if (rs.next()) {
                user = rs2class(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;

    }

    public static void updateUser(User user) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            PreparedStatement stat = conn.prepareStatement("insert into `" + DBUSER + "` (`username`,`passwd`,`familyname`,`name`,`email`,`identity`,`staff`)" +
                    "values(?,?,?,?,?,?,?)");
            if (DB.isDebug()) {
                System.out.println("UserDao.updateUser() processing");
                JSONObject jb = JSONObject.fromObject(user);
                String tojson = jb.toString();
                System.out.println("user for update");
                System.out.println(tojson);
            }

            stat.setString(1, user.getUsername());
            stat.setString(2, user.getPasswd());

            stat.setString(3, user.getFamilyname());
            stat.setString(4, user.getName());
            stat.setString(5, user.getEmail());
            stat.setString(6, user.getIdentity());
            stat.setString(7,user.getStaff());
            if (DB.isDebug()) {
                System.out.println("Complete stat.setString()");
                System.out.println(stat);
            }
            stat.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static String getImage(String username) {
        String base64Image = null;
        connector();
        if (DB.isDebug()) {
            System.out.println("getting image");
        }
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            PreparedStatement stat = conn.prepareStatement("select * from " + DBIMAGE + " where username = ?");

            stat.setString(1, username);
            ResultSet rs = stat.executeQuery();

            if (!rs.next()) {
                stat.setString(1, "admin");
                rs = stat.executeQuery();
                rs.next();
            }

            Blob blob = rs.getBlob("portrait");
            InputStream inputStream = blob.getBinaryStream();
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            byte[] buffer = new byte[4096];
            int bytesRead = -1;

            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }

            byte[] imageBytes = outputStream.toByteArray();
            base64Image = Base64.getEncoder().encodeToString(imageBytes);

            inputStream.close();
            outputStream.close();

        } catch (SQLException | IOException e) {
            e.printStackTrace();

        }
        return base64Image;
    }

    public static void  updateportrait(String username, InputStream portrait) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("processing updateportrait");
            }
            PreparedStatement stat = conn.prepareStatement(" insert into `" + DBIMAGE + "` (`username`,`portrait`) values (?,?)");
            stat.setString(1, username);
            stat.setBlob(2, portrait);
            stat.execute();
            if (DB.isDebug()) System.out.println("updateportrait complete");

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 根据用户名删除数据库的记录
     *
     * @param username
     * @return 运行状态值,1表示成功, 0表示失败
     */
    public static int deleteUser(String username) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("processing deleteUser");
            }
            PreparedStatement stat = conn.prepareStatement("delete from `" + DBUSER + "` where username=?");
            stat.setString(1, "'" + username + "'");
            int result = stat.executeUpdate();
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static void updatePrice(String username, int price){
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("processing updatePrice");
            }
            PreparedStatement stat = conn.prepareStatement("update `" + DBUSER + "` set `price`=`price`- " + price +
                    " where username=?");
            stat.setString(1, "'" + username + "'");
            int result = stat.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
